package com.example.dao;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.example.domain.ClassCourse;

@Repository
public interface ClassCourseDao extends JpaRepository<ClassCourse, Integer> {

    @Transactional
    @Modifying
    Integer deleteByClassCourseId(Integer classCourseId);

    List<ClassCourse> findByClassCourseId(Integer classCourseId);

    @Transactional
    @Modifying
    Integer deleteByClassCourseIdIn(List<Integer> listId);

    @Query(nativeQuery = true, value = "SELECT c.class_Id as classId,t.termName,c.className \r\n" +
            "from classes c left join term t \r\n" +
            "on c.term_Id = t.term_Id\r\n" +
            "where c.class_Id not in (select cc.class_Id from class_course cc)")
    List<Map<String, Object>> queryAllClassNameOfNotAllocation();

    @Query(nativeQuery = true, value = "select t.termName,cl.className,co.courseName,cc.* \r\n" +
            "from ((classes cl INNER JOIN term t ON cl.term_Id = t.term_Id )\r\n" +
            "INNER JOIN class_course cc ON cl.class_Id = cc.class_Id)\r\n" +
            "INNER JOIN course co ON cc.course_Id = co.course_Id")
    List<Map<String, Object>> queryAllCourseAndClassInfo();

    @Query(nativeQuery = true, value = "select t.termName,cl.className,co.courseName,cc.* \r\n" +
            "from ((classes cl INNER JOIN term t ON cl.term_Id = t.term_Id )\r\n" +
            "INNER JOIN class_course cc ON cl.class_Id = cc.class_Id)\r\n" +
            "INNER JOIN course co ON cc.course_Id = co.course_Id\r\n" +
            "WHERE t.termName like CONCAT('%',:termName,'%')")
    List<Map<String, Object>> findByTermNameLike(@Param("termName") String termName);

    @Query(nativeQuery = true, value = "select t.termName,cl.className,co.courseName,cc.* \r\n" +
            "from ((classes cl INNER JOIN term t ON cl.term_Id = t.term_Id )\r\n" +
            "INNER JOIN class_course cc ON cl.class_Id = cc.class_Id)\r\n" +
            "INNER JOIN course co ON cc.course_Id = co.course_Id\r\n" +
            "WHERE cl.className like CONCAT('%',:className,'%')")
    List<Map<String, Object>> findByClassNameLike(@Param("className") String className);

    @Query(nativeQuery = true, value = "select t.termName,cl.className,co.courseName,cc.* \r\n" +
            "from ((classes cl INNER JOIN term t ON cl.term_Id = t.term_Id )\r\n" +
            "INNER JOIN class_course cc ON cl.class_Id = cc.class_Id)\r\n" +
            "INNER JOIN course co ON cc.course_Id = co.course_Id\r\n" +
            "WHERE co.courseName like CONCAT('%',:courseName,'%')")
    List<Map<String, Object>> findByCourseNameLike(@Param("courseName") String courseName);

}
